﻿using System.Data.SqlClient;

namespace AccountTransactions
{
    public class Program
    {
        public static void CreateAccountTransactionsTable(
            SqlConnection dbCon)
        {
            SqlCommand createTableCommand = new SqlCommand();
            createTableCommand.Connection = dbCon;
            createTableCommand.CommandText =
                "CREATE TABLE AccountTransactions( " +
                "Id int IDENTITY PRIMARY KEY, " +
                "CardNumber char(10) NOT NULL, " +
                "TransactionDate datetime NOT NULL, " +
                "Amount money NOT NULL)";
            createTableCommand.ExecuteNonQuery();
        }

        public static void CreateTriggerAfterWithdrawal(SqlConnection dbCon)
        {
            SqlCommand createTableCommand = new SqlCommand();
            createTableCommand.Connection = dbCon;
            createTableCommand.CommandText =
                "CREATE TRIGGER WithdrawMoney ON CardAccounts " + 
                "FOR UPDATE " + 
                "AS " +
                    "INSERT INTO AccountTransactions (CardNumber, " +
                        "TransactionDate, Amount) " + 
	                "SELECT " +
                        "i.CardNumber, GETDATE(), d.CardCash - i.CardCash " +
                    "FROM inserted AS i, deleted as d ";
            createTableCommand.ExecuteNonQuery();
        }

        public static void Main()
        {
            SqlConnection dbCon = new SqlConnection(
                "Server=.\\SQLEXPRESS; Database=atm; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                CreateTriggerAfterWithdrawal(dbCon);
            }
        }
    }
}
